JDBC: detecting lost backend; 8192 byte limit in queries

Hi,

I've been fairly happily using the PostgreSQL JDBC driver. I've run into a
snag, though, when the backend dies. The driver never detects that the
socket to the backend is lost and it will throw IO exceptions whenever a
statement using an old connection is executed. This is a problem for me
because I keep connections open for long periods of time in a connection
pool. My temporary workaround for this has been to use a wrapper around the
SQL statement class that traps all exceptions and examines them to
determine if they indicate a lost connection. The messages associated with
lost connections start with the following strings:

    "The backend has broken the connection.",
    "IOError while reading from backend:",
    "Error flushing output:",
    "I/O Error:",

I selected these messages based on examining the Java source for the
driver. When my statement wrapper detects one of these errors while using
the PostgreSQL JDBC driver it then knows that the connection is lost and
calls the connection's close() method. What would be much nicer, and
eliminate this ugly hack from my code, is if the driver would notice when
one of these errors occurs and set its isClosed() flag to true.

The other thing that has been making me reconsider using PostgreSQL is the
8192 SQL statement limit. The problem is that I want to store large amounts
of data, e.g., large messages, but when I try to do an SQL insert
containing the data the driver throws an SQL exception because the
statement is too large. I realize this limit is in the postgres backend
sources (it appears, among other places, as a static size in the statement
parser), but wish there were some way around this so I could continue to
use PostgreSQL and not have to switch to mySQL with its lack of transaction
support.

So, to summarize:

- driver needs a fix to set the isClosed() flag when the connection to the
backend is lost

- how do I store more than 8192 bytes?

Versions/systems/etc.: PostgreSQL 6.4.2, Solaris JDK1.2; PostgreSQL
snapshot from (I think) January 1st on RHLinux 5.1 with Blackdown JDK
1.1.7v1a.

-- Ari Halberstadt mailto:ari@shore.net <http://www.magiccookie.com/>
PGP public key available at <http://www.magiccookie.com/pgpkey.txt>